# coding: utf-8
from sqlalchemy import Column, Integer, String
from app.extensions import db


class SjmlMenuTable(db.Model):
    __tablename__ = 'sjml_menu_table'

    id = Column(Integer, primary_key=True, autoincrement=True, comment='ID')
    menu_id = Column(Integer, nullable=True, comment='菜单ID')
    table_name = Column(String(255), nullable=True, comment='表名')
    system_name = Column(String(255), nullable=True, comment='系统名称')
    system_id = Column(Integer, nullable=True, comment='系统ID')

    def to_dict(self):
        return {
            'id': self.id,
            'menu_id': self.menu_id,
            'table_name': self.table_name,
            'system_name': self.system_name,
            'system_id': self.system_id
        }
        
    @classmethod
    def get_by_menu_id(cls, menu_id):
        """
        根据menu_id获取菜单下的表数据
        
        Args:
            menu_id: 菜单ID
            
        Returns:
            list: 表数据列表
        """
        try:
            tables = db.session.query(cls).filter(cls.menu_id == menu_id).all()
            return tables
        except Exception as e:
            print(f'【根据menu_id获取表数据错误:】{str(e)}')
            db.session.rollback()
            raise e


class SjmuMenuSql(db.Model):
    __tablename__ = 'sjml_menu_sql'

    id = Column(Integer, primary_key=True, autoincrement=True, comment='ID')
    menu_id = Column(Integer, nullable=True, comment='菜单ID')
    sql_text = Column(String(255), nullable=True, comment='SQL文本')
    system_name = Column(String(255), nullable=True, comment='系统名称')
    system_id = Column(Integer, nullable=True, comment='系统ID')
    table_id = Column(Integer, nullable=True, comment='表ID')

    def to_dict(self):
        return {
            'id': self.id,
            'menu_id': self.menu_id,
            'sql_text': self.sql_text,
            'system_name': self.system_name,
            'system_id': self.system_id,
            'table_id': self.table_id
        }
        
    @classmethod
    def get_by_menu_id(cls, menu_id):
        """
        根据menu_id获取菜单下的SQL数据
        
        Args:
            menu_id: 菜单ID
            
        Returns:
            list: SQL数据列表
        """
        try:
            sqls = db.session.query(cls).filter(cls.menu_id == menu_id).all()
            return sqls
        except Exception as e:
            print(f'【根据menu_id获取SQL数据错误:】{str(e)}')
            db.session.rollback()
            raise e


class SjmlMenu(db.Model):
    __tablename__ = 'sjml_menu'

    id = Column(Integer, primary_key=True, autoincrement=True, comment='菜单ID')
    menu_name = Column(String(255), nullable=True, comment='菜单名称')
    menu_level = Column(Integer, nullable=True, comment='菜单级别')
    parent_id = Column(Integer, nullable=True, comment='父菜单ID')
    system_name = Column(String(255), nullable=True, comment='系统名称')
    system_id = Column(Integer, nullable=True, comment='系统ID')
    conversation_id = Column(String(36), nullable=False, comment='会话ID')
    record_id = Column(String(36), nullable=False, comment='记录ID')

    def to_dict(self):
        return {
            'id': self.id,
            'menu_name': self.menu_name,
            'menu_level': self.menu_level,
            'parent_id': self.parent_id,
            'system_name': self.system_name,
            'system_id': self.system_id,
            'conversation_id': self.conversation_id,
            'record_id': self.record_id
        }
    
    @classmethod
    def get_by_record_id(cls, record_id):
        """
        根据record_id获取菜单记录
        
        Args:
            record_id: 记录ID
            
        Returns:
            list: 菜单记录列表
        """
        try:
            menus = db.session.query(cls).filter(cls.record_id == record_id).all()
            return menus
        except Exception as e:
            print(f'【根据record_id获取菜单记录错误:】{str(e)}')
            db.session.rollback()
            raise e
            
    @classmethod
    def build_menu_tree(cls, menus):
        """
        将菜单列表构建成树状结构
        
        Args:
            menus: 菜单记录列表
            
        Returns:
            list: 树状结构的菜单列表
        """
        # 转换为字典格式
        menu_dict = {}
        menu_list = []
        
        # 首先将所有菜单放入字典中，key为菜单ID
        for menu in menus:
            menu_data = menu.to_dict()
            menu_data['children'] = []
            menu_dict[menu_data['id']] = menu_data
            menu_list.append(menu_data)
        
        # 构建树状结构
        tree = []
        for menu in menu_list:
            parent_id = menu['parent_id']
            if parent_id is None or parent_id == 0 or parent_id not in menu_dict:
                # 根菜单
                tree.append(menu)
            else:
                # 子菜单，添加到父菜单的children中
                menu_dict[parent_id]['children'].append(menu)
        
        return tree
    
    @classmethod
    def get_menu_tree_by_record_id(cls, record_id):
        """
        根据record_id获取菜单树状结构
        
        Args:
            record_id: 记录ID
            
        Returns:
            list: 树状结构的菜单列表
        """
        menus = cls.get_by_record_id(record_id)
        if not menus:
            return []
        return cls.build_menu_tree(menus)